#!/usr/bin/perl

######################################################################
#
# freqs2xls.pl
#
# $Id: freqs2xls.pl 188 2014-02-24 02:50:52Z drj826@gmail.com $
#
# Convert freqency listing (CSV format) to an Excel spreadsheet.
#
#---------------------------------------------------------------------
# USAGE (execution)
#
#   perl freqs2xls.pl --skip 1 --order 22 -- 2m.csv 70cm.csv ...
#
#     --outfile | -x => name of output xlsx file
#     --channel | -c => column containing channel list
#     --order   | -o => order by values in specified column
#     --filter  | -f => filter on specified columns
#     --skip    | -s => comma separated list of lines to skip
#     --shade        => shade odd rows lite grey
#
#---------------------------------------------------------------------
# Description
#
#   I use these CSV column names in my Google Apps spreadsheet of
#   frequencies and repeater channels.  Some of them correspond to
#   those used by the Chirp radio programming software
#   (http://chirp.danplanet.com/).  This makes it easy to program my
#   radios using channels and frequencies from the spreadsheet.
#
# VHF/UHF CSV Columns
# ---+----------------+-----------------------------------------
#  0 | Frequency      | in MHz
#  1 | Duplex         | +/-
#  2 | Offset         | in MHz
#  3 | Tone           | Tone, TSQL
#  4 | rToneFreq      | encode tone (Hz)
#  5 | cToneFreq      | decode tone (Hz)
#  6 | DtcsCode       | DCS code
#  7 | DtcsPolarity   |
#  8 | Mode           | FM/NFM
#  9 | Coverage       | wide area/local
# 10 | Area           | major area
# 11 | Location       | repeater location
# 12 | Sponsor        |
# 13 | SponsorAcronym |
# 14 | SponsorURL     |
# 15 | Callsign       |
# 16 | Label 6        | 6 character label
# 17 | Label 7        | 7 character label
# 18 | Label 8        | 8 character label
# 19 | Use            | ham, law, fire, ems, sar, mun, pub, wx
# 20 | Comment        |
# 21 | Skip           | 'S' if channel should be skipped during scanning
# 22 | Power          | Low
# 23 | Ch List A      | channel number in radios with 500+ memories
#
######################################################################

use strict;
use Text::CSV;
use Getopt::Long qw(:config no_ignore_case);

use Excel::Writer::XLSX;
use Excel::Writer::XLSX::Worksheet;

my $order;                              # column on which to sort records
my $xlsfile;                            # name of output xlsx file
my $channel;                            # column containing channel list
my @skip;                               # skip this lines
my @filter;                             # show only rows with content in these columns

GetOptions
  (
   "outfile|x=s" => \$xlsfile,
   "order|o=s"   => \$order,
   "channel|c=s" => \$channel,
   "skip|s=s"    => \@skip,
   "filter|f=s"  => \@filter,
  );

@skip = split(',',join(',',@skip));

my %skip_line;                          # lines to skip
foreach my $line (@skip) {
  $skip_line{$line} = 1;
}

# background colors
#
#  1 => green
#  2 => red
#  3 => lite green
#  4 => blue
#  5 => yellow
#  6 => magenta
#  7 => cyan
#  8 => black
#  9 => white
# 10 => red
# 11 => lite green
# 12 => blue
# 13 => yellow

my $data    = read_csv_files(@ARGV);
my $wb      = Excel::Writer::XLSX->new($xlsfile);

$wb->set_custom_color(41,255,255,255,); # white
$wb->set_custom_color(42,173,216,230);  # light blue
$wb->set_custom_color(43,144,238,144);  # light green
$wb->set_custom_color(44,255,255,0);    # yellow
$wb->set_custom_color(45,255,165,0);    # orange
$wb->set_custom_color(46,240,128,128);  # light coral
$wb->set_custom_color(47,211,211,211);  # light gray
$wb->set_custom_color(48,240,240,240);  # shade
$wb->set_custom_color(49,210,180,140);  # tan
$wb->set_custom_color(50,221,160,221);  # plum

my $ws      = $wb->add_worksheet('channels');
my $white   = $wb->add_format(bg_color=>'41',align=>'center',valign=>'top');
my $blue    = $wb->add_format(bg_color=>'42',align=>'center',valign=>'top');
my $green   = $wb->add_format(bg_color=>'43',align=>'center',valign=>'top');
my $yellow  = $wb->add_format(bg_color=>'44',align=>'center',valign=>'top');
my $orange  = $wb->add_format(bg_color=>'45',align=>'center',valign=>'top');
my $red     = $wb->add_format(bg_color=>'46',align=>'center',valign=>'top');
my $grey    = $wb->add_format(bg_color=>'47',align=>'center',valign=>'top');
my $tan     = $wb->add_format(bg_color=>'49',align=>'center',valign=>'top');
my $plum    = $wb->add_format(bg_color=>'50',align=>'center',valign=>'top');

my $left    = $wb->add_format(align=>'left',num_format=>'@',text_wrap=>1,valign=>'top');
my $right   = $wb->add_format(align=>'right',num_format=>'@',text_wrap=>1,valign=>'top');
my $center  = $wb->add_format(align=>'center',valign=>'top');
my $head    = $wb->add_format(bold=>1,align=>'left',text_wrap=>1,valign=>'top');

my $odd     = $wb->add_format(align=>'left',num_format=>'@',bg_color=>'48',valign=>'top');
my $even    = $wb->add_format(align=>'left',num_format=>'@',valign=>'top');

my $c = 0;                              # column number
my $r = 0;                              # row number

foreach my $column (@{ get_column_list() })
  {
    my $heading = $column->[0];
    my $width   = $column->[1];
    my $format  = $column->[2];

    $ws->set_column($c,$c,$width,$format);
    $ws->write($r,$c,$heading,$head);

    ++ $c                               # increment column number
  }

++ $r;                                  # increment row number

my $ord = 'odd';

foreach my $record (@{ get_channel_list() })
  {
    if ( is_filtered($record) )
      {
	next;
      }

    add_row($record,$ord);
    ++ $r;                              # increment row number

    if ( $ord eq 'odd' )
      {
	$ord = 'even';
      }

    else
      {
	$ord = 'odd';
      }
  }

$ws->freeze_panes(1,0);

######################################################################

sub get_column_list {

  my $left   = shift;
  my $center = shift;

  return
    [
     # heading      width  format
     # -----------  -----  -------
     ['ch',             4, $center ],
     ['label',          9, $left   ],
     ['frequency',     15, $left   ],
     ['offset',         7, $left   ],
     ['access',        16, $left   ],
     ['location',      25, $left   ],
     ['sponsor',       35, $left   ],
     ['notes',         35, $left   ],
    ];
}

######################################################################

sub add_row {

  my $record = shift;
  my $ord    = shift;                   # even or odd
  my $c      = 0;                       # column number
  my $d      = {};                      # data hash
  my $f      = {};                      # format hash

  $d->{channel}   = get_channel($record);
  $d->{label}     = get_label($record);
  $d->{frequency} = get_frequency($record);
  $d->{offset}    = get_offset($record);
  $d->{access}    = get_access($record);
  $d->{location}  = get_location($record);
  $d->{sponsor}   = get_sponsor($record);
  $d->{notes}     = get_notes($record);

  $f->{channel}   = get_label_format($record);

  if ( $ord eq 'odd' )
    {
      $f->{label}     = $odd;
      $f->{frequency} = $odd;
      $f->{offset}    = $odd;
      $f->{access}    = $odd;
      $f->{location}  = $odd;
      $f->{sponsor}   = $odd;
      $f->{notes}     = $odd;
    }

  else
    {
      $f->{label}     = $even;
      $f->{frequency} = $even;
      $f->{offset}    = $even;
      $f->{access}    = $even;
      $f->{location}  = $even;
      $f->{sponsor}   = $even;
      $f->{notes}     = $even;
    }

  #-------------------------------------------------------------------
  # write row
  #
  $ws->write($r,$c,  $d->{channel},   $f->{channel});
  $ws->write($r,++$c,$d->{label},     $f->{label});
  $ws->write($r,++$c,$d->{frequency}, $f->{frequency});
  $ws->write($r,++$c,$d->{offset},    $f->{offset});
  $ws->write($r,++$c,$d->{access},    $f->{access});
  $ws->write($r,++$c,$d->{location},  $f->{location});
  $ws->write($r,++$c,$d->{sponsor},   $f->{sponsor});
  $ws->write($r,++$c,$d->{notes},     $f->{notes});

  return 1;
}

######################################################################

sub get_channel {
  my $record = shift;
  return $record->[$channel];
}

######################################################################

sub get_label {
  my $record = shift;
  return $record->[16];
}

######################################################################

sub get_label_format {

  my $record = shift;
  my $use    = $record->[19];

  if ( $use eq 'ham' )
    {
      return $plum;
    }

  elsif ( $use eq 'law' )
    {
      return $blue;
    }

  elsif ( $use eq 'fire' )
    {
      return $orange;
    }

  elsif ( $use eq 'ems' )
    {
      return $yellow;
    }

  elsif ( $use eq 'sar' )
    {
      return $green;
    }

  elsif ( $use eq 'pub' )
    {
      return $grey;
    }

  elsif ( $use eq 'wx' )
    {
      return $grey;
    }

  elsif ( $use eq 'bus' )
    {
      return $tan;
    }

  else
    {
      return $white;
    }
}

######################################################################

sub get_frequency {
  my $record = shift;
  return $record->[0] . " " . $record->[8];
}

######################################################################

sub get_offset {
  my $record = shift;

  my $duplex = $record->[1];
  my $offset = $record->[2];

  if ( $offset and $duplex eq '-' )
    {
      return '-' . $offset;
    }

  elsif ( $offset and $duplex eq '+' )
    {
      return '+' . $offset;
    }

  elsif ( not $offset )
    {
      return 'simplex';
    }
}

######################################################################

sub get_access {

  my $record = shift;
  my $string = q{};

  if ($record->[3])
    {
      $string .= "$record->[3] ";       # tone type
    }

  if ($record->[4])
    {
      $string .= "$record->[4] ";       # encode tone
    }

  if ($record->[5])
    {
      $string .= "/$record->[5] ";      # decode tone
    }

  if ($record->[6])
    {
      $string .= "DCS$record->[6] ";    # DCS code
    }

  return $string;
}

######################################################################

sub get_location {

  my $record = shift;
  my $string = q{};

  if ($record->[10])
    {
      $string .= "$record->[10]";
    }

  if ($record->[11])
    {
      $string .= ", $record->[11]";
    }

  return $string;
}

######################################################################

sub get_sponsor {

  my $record = shift;
  my $string = q{};

  if ($record->[15])
    {
      $string .= "$record->[15]";       # call sign
    }

  if ($record->[15] and $record->[12])
    {
      $string .= ", ";                  # separator
    }

  if ($record->[12])
    {
      $string .= "$record->[12]";       # sponsor name (long)
    }

  if ($record->[13])
    {
      $string .= " ($record->[13])";    # sponsor acronym
    }

  return $string;
}

######################################################################

sub get_notes {
  my $record = shift;
  return $record->[20];
}

######################################################################

sub get_channel_list {

  if ( $order )
    {
      return [ sort by_order @{ $data } ];
    }

  else
    {
      return $data;
    }
}

######################################################################

sub is_filtered {

  my $record = shift;

  foreach my $column (@filter)
    {
      if ( not $record->[$column] )
	{
	  return 1;
	}
    }

  return 0;
}

######################################################################

sub read_csv_files {

  my @files = @_;
  my $data  = [];

  foreach my $file (@files)
    {
      my $line = 0;

      if (not -r $file)
	{
	  print "file not readable: \"$file\"\n";
	  next;
	}

      open my $fh, "< :encoding(UTF-8)", $file or die "$file not readable";
      while (<$fh>) {
	++ $line;
	next if $skip_line{$line};
	my $csv    = Text::CSV->new();
	my $status = $csv->parse($_);
	my $record = [ $csv->fields() ];
	push( @{$data}, $record );
      }
      close $fh;

    }

  return $data;
}

######################################################################

sub by_order {

  my $a_val = $a->[$order];
  my $b_val = $b->[$order];

  if ( $a_val =~ /[a-z]/i or $b_val =~ /[a-z]/i )
    {
      return lc($a_val) cmp lc($b_val);
    }

  else
    {
      return $a_val <=> $b_val;
    }
}

######################################################################

1;
